{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "from sklearn.cluster import KMeans\n",
    "import re\n",
    "import string\n",
    "from collections import defaultdict\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "成功读取Excel文件，共4892行数据\n",
      "进行全局聚类，聚类数量: 100\n",
      "运行中\n",
      "聚类结果预览:\n",
      "   总表序号 原表序号                   报表名称  报表类型                           报送周期  \\\n",
      "0     1    1                理论宣讲统计表  固定报表  2024年1-6月，每月报送；2024年7月至今，每季报送   \n",
      "1     2    2     党委（党组）中心组学习外请报告备案表  临时报表                           据情报送   \n",
      "2     3    3              包河区印刷企业台账  临时报表                          按通知报送   \n",
      "3     4    4              包河区旧书店统计表  临时报表                          按通知报送   \n",
      "4     5    5  2025年4月“绿书签行动”宣传活动统计表  临时报表                          按通知报送   \n",
      "\n",
      "          填报主体     报送渠道   需求部门 报送层级           业务事项名称         上报区划   字段数  文件格式  \\\n",
      "0  芜湖路街道党建工作中心       邮箱  区委宣传部  街道级           理论宣讲统计  合肥市包河区芜湖路街道   5.0  xlsx   \n",
      "1  芜湖路街道党建工作中心  纸质或微信报送  区委宣传部  街道级   中心组学习外请人员作报告备案  合肥市包河区芜湖路街道   6.0   doc   \n",
      "2  芜湖路街道党建工作中心       微信  区委宣传部  街道级         摸排辖区印刷企业  合肥市包河区芜湖路街道  12.0  xlsx   \n",
      "3  芜湖路街道党建工作中心       微信  区委宣传部  街道级          摸排辖区旧书店  合肥市包河区芜湖路街道   4.0  xlsx   \n",
      "4  芜湖路街道党建工作中心       微信  区委宣传部  街道级  统计“绿书签行动”宣传活动情况  合肥市包河区芜湖路街道   6.0   doc   \n",
      "\n",
      "  是否系统报送                         匹配部门  全局聚类ID       全局代表性报表  \n",
      "0    NaN  中共安徽省委宣传部（省新闻出版局、省版权局、省电影局）       2        固定资产月报  \n",
      "1    NaN  中共安徽省委宣传部（省新闻出版局、省版权局、省电影局）       2        固定资产月报  \n",
      "2    NaN  中共安徽省委宣传部（省新闻出版局、省版权局、省电影局）       2        固定资产月报  \n",
      "3    NaN  中共安徽省委宣传部（省新闻出版局、省版权局、省电影局）       2        固定资产月报  \n",
      "4    NaN  中共安徽省委宣传部（省新闻出版局、省版权局、省电影局）       1  “小金库”自查情况统计表  \n"
     ]
    }
   ],
   "source": [
    "file_path = \"C:/Users/xingwenzheng/Desktop/报表及字段.xlsx\"\n",
    "sheet_name=\"汇总部门\"  # 根据实际sheet名称修改\n",
    "department_col=\"匹配部门\"  # 根据实际列名调整\n",
    "report_col=\"报表名称\"  # 根据实际列名调整\n",
    "# 设置总的聚类数量\n",
    "total_clusters = 100\n",
    "# 处理Excel文件\n",
    "df = pd.read_excel(file_path, sheet_name)\n",
    "print(f\"成功读取Excel文件，共{len(df)}行数据\")\n",
    "\n",
    "# 检查必要的列是否存在\n",
    "if department_col not in df.columns:\n",
    "    print(f\"Excel文件中没有找到'{department_col}'列\")\n",
    "    print(f\"可用列: {list(df.columns)}\")\n",
    "\n",
    "if report_col not in df.columns:\n",
    "    print(f\"Excel文件中没有找到'{report_col}'列\")\n",
    "    print(f\"可用列: {list(df.columns)}\")\n",
    "\n",
    "# 首先在整个数据集上进行向量化\n",
    "all_reports = df[report_col].tolist()\n",
    "vectorizer = TfidfVectorizer(min_df=1, norm='l2')\n",
    "try:\n",
    "    X_all = vectorizer.fit_transform(all_reports)\n",
    "    X_all_array = X_all.toarray()\n",
    "except Exception as e:\n",
    "    print(f\"全局向量化时出错: {e}\")\n",
    "\n",
    "# 如果指定了总聚类数量，进行全局聚类\n",
    "if total_clusters is not None:\n",
    "    # 确保聚类数量不超过报表总数\n",
    "    n_global_clusters = min(total_clusters, len(all_reports))\n",
    "    print(f\"进行全局聚类，聚类数量: {n_global_clusters}\")\n",
    "\n",
    "    try:\n",
    "        kmeans_global = KMeans(n_clusters=n_global_clusters, random_state=42, n_init=10)\n",
    "        global_clusters = kmeans_global.fit_predict(X_all_array)\n",
    "\n",
    "        # 为每个全局聚类找到代表性报表\n",
    "        global_cluster_reports = defaultdict(list)\n",
    "        global_cluster_vectors = defaultdict(list)\n",
    "\n",
    "        for i, cluster_id in enumerate(global_clusters):\n",
    "            global_cluster_reports[cluster_id].append(df[report_col].iloc[i])\n",
    "            global_cluster_vectors[cluster_id].append(X_all_array[i])\n",
    "            \n",
    "        print(\"运行中\")\n",
    "        # 为每个全局聚类找到代表性报表\n",
    "        global_representative_reports = {}\n",
    "        for cluster_id, reports in global_cluster_reports.items():\n",
    "            vectors = global_cluster_vectors[cluster_id]\n",
    "            representative = find_centroid_report(reports, vectorizer, vectors)\n",
    "            global_representative_reports[cluster_id] = representative\n",
    "\n",
    "        # 添加全局聚类结果到DataFrame\n",
    "        df['全局聚类ID'] = global_clusters\n",
    "        df['全局代表性报表'] = df['全局聚类ID'].map(global_representative_reports)\n",
    "\n",
    "    except Exception as e:\n",
    "        print(f\"全局聚类时出错: {e}\")\n",
    "\n",
    "print(\"聚类结果预览:\")\n",
    "print(df.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理Excel文件\n",
    "original_df = pd.read_excel(file_path)\n",
    "result_df = process_excel_with_clustering(\n",
    "    file_path,\n",
    "    sheet_name=\"汇总部门\",  # 根据实际sheet名称修改\n",
    "    department_col=\"匹配部门\",  # 根据实际列名调整\n",
    "    report_col=\"报表名称\"  # 根据实际列名调整\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'报名称'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2894\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2895\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcasted_key\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2896\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: '报名称'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-16-0efbd1fef3aa>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 预处理报表名称\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'processed_report'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcleaned_report\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpreprocess_text\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# 按部门分组\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mgrouped\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdepartment_col\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2900\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnlevels\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2901\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2902\u001b[1;33m             \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2903\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mis_integer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2904\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2895\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcasted_key\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2896\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2897\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2898\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2899\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mtolerance\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: '报名称'"
     ]
    }
   ],
   "source": [
    "def process_excel_with_clustering(file_path, sheet_name, department_col, report_col):\n",
    "    # 读取Excel文件\n",
    "    try:\n",
    "        df = pd.read_excel(file_path, sheet_name=sheet_name)\n",
    "        print(f\"成功读取Excel文件，共{len(df)}行数据\")\n",
    "    except Exception as e:\n",
    "        print(f\"读取Excel文件时出错: {e}\")\n",
    "        return None\n",
    "\n",
    "\n",
    "file_path = \"C:/Users/xingwenzheng/Desktop/报表及字段.xlsx\"\n",
    "# 处理Excel文件\n",
    "original_df = pd.read_excel(file_path)\n",
    "result_df = process_excel_with_clustering(\n",
    "    file_path,\n",
    "    sheet_name=\"汇总部门\",  # 根据实际sheet名称修改\n",
    "    department_col=\"匹配部门\",  # 根据实际列名调整\n",
    "    report_col=\"报表名称\"  # 根据实际列名调整\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
